﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ClassLibrary.Data
{
    public class DBService
    {
        #region Variables
        private SqlConnection _conn;
        private string _connectionString;
        #endregion

        #region Construction

        public DBService(string connectionString)
        {
            _connectionString = connectionString;
        }
        #endregion

        #region Private Methods
        private SqlConnection OpenSqL()
        {
            try
            {
                _conn = new SqlConnection(_connectionString);
                if (_conn.State == ConnectionState.Closed)
                {
                    _conn.Open();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return _conn;
        }

        private void CloseSql()
        {
            try
            {
                if (_conn != null)
                {
                    if (_conn.State != ConnectionState.Closed)
                    {
                        _conn.Dispose();
                        _conn.Close();
                        _conn = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable CreatDataTable(SqlDataReader dr)
        {
            try
            {
                DataTable table = new DataTable();
                DataRow row;
                int fieldcount = dr.FieldCount - 1;
                for (int i = 0; i <= fieldcount; i++)
                    table.Columns.Add(dr.GetName(i));
                while (dr.Read())
                {
                    row = table.NewRow();
                    for (int i = 0; i <= fieldcount; i++)
                    {
                        try
                        {
                            row[i] = dr[i].ToString();
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                    table.Rows.Add(row);
                }
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
            }
        }

        private DataTable CreatDataTable_No(SqlDataReader dr)
        {
            try
            {
                DataTable table = new DataTable();
                DataRow row;
                int j;
                int fieldcount = dr.FieldCount - 1;
                for (int i = 0; i <= fieldcount + 1; i++)
                {
                    if (i == 0)
                        table.Columns.Add("No");
                    else
                        table.Columns.Add(dr.GetName(i - 1));
                }
                j = 0;
                while (dr.Read())
                {
                    row = table.NewRow();
                    for (int i = 0; i <= fieldcount + 1; i++)
                    {
                        try
                        {
                            if (i == 0)
                                row[i] = j + 1;
                            else
                                row[i] = dr[i - 1].ToString();
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                    table.Rows.Add(row);
                    j = j + 1;
                }
                return table;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
            }
        }
        #endregion

        #region Public Methods
        public DataTable ExeSP_DataTable(string sp_name, string[] param, object[] values, bool OrderNum, CommandType commandType)
        {
            SqlDataReader dr = null;
            SqlCommand command = null;
            DataTable dt;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                if ((!(param == null)))
                {
                    int len = param.Length - 1;
                    for (int i = 0; i <= len; i++)
                        command.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (OrderNum == false)
                    dt = CreatDataTable(dr);
                else
                    dt = CreatDataTable_No(dr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
                CloseSql();
            }
            return dt;
        }

        public DataTable ExeSP_DataTable(string sp_name, bool OrderNum, CommandType commandType)
        {
            SqlDataReader dr = null;
            SqlCommand command = null;
            DataTable dt;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (OrderNum == false)
                    dt = CreatDataTable(dr);
                else
                    dt = CreatDataTable_No(dr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
                CloseSql();
            }
            return dt;
        }

        public DataTable ExeSP_DataTable(string sp_name, string[] param, object[] values, CommandType commandType)
        {
            SqlDataReader dr = null;
            SqlCommand command = null;
            DataTable dt;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                if ((!(param == null)))
                {
                    int len = param.Length - 1;
                    for (int i = 0; i <= len; i++)
                        command.Parameters.Add(new SqlParameter(param[i], values[i]));
                }

                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                dt = CreatDataTable(dr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
                CloseSql();
            }
            return dt;
        }

        public DataTable ExeSP_DataTable(SqlCommand command)
        {
            DataTable dt;
            SqlDataReader dr = null;

            try
            {
                command.Connection = OpenSqL();
                command.CommandTimeout = 0;

                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                dt = CreatDataTable(dr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
                CloseSql();
            }
            return dt;
        }

        public DataSet ExeSP_DataSet(string sp_name, CommandType commandType)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                da = new SqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (da != null)
                {
                    da.Dispose();
                    da = null;
                }
                CloseSql();
            }
            return ds;
        }

        public DataSet ExeSP_DataSet(string sp_name, string tableName, CommandType commandType)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                da = new SqlDataAdapter(command);
                da.Fill(ds, tableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (da != null)
                {
                    da.Dispose();
                    da = null;
                }
                CloseSql();
            }
            return ds;
        }

        public DataSet ExeSP_DataSet(string sp_name, string[] param, object[] values, CommandType commandType)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                if ((!(param == null)))
                {
                    int len = param.Length - 1;
                    for (int i = 0; i <= len; i++)
                        command.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
                da = new SqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (da != null)
                {
                    da.Dispose();
                    da = null;
                }
                CloseSql();
            }
            return ds;
        }

        public DataSet ExeSP_DataSet(string sp_name, string[] param, object[] values, string tableName, CommandType commandType)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                if ((!(param == null)))
                {
                    int len = param.Length - 1;
                    for (int i = 0; i <= len; i++)
                        command.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
                da = new SqlDataAdapter(command);
                da.Fill(ds, tableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (da != null)
                {
                    da.Dispose();
                    da = null;
                }
                CloseSql();
            }
            return ds;
        }

        public int ExeSP_NonQuery(string sp_name, CommandType commandType)
        {
            int result = 0;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                result = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                CloseSql();
            }
            return result;
        }

        public int ExeSP_NonQuery(string sp_name, string[] param, object[] values, CommandType commandType)
        {
            int result = 0;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                if ((!(param == null)))
                {
                    int len = param.Length - 1;
                    for (int i = 0; i <= len; i++)
                        command.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
                result = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                CloseSql();
            }
            return result;
        }

        public int ExeSP_NonQuery(SqlCommand command)
        {
            try
            {
                command.Connection = OpenSqL();
                command.CommandTimeout = 0;

                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                CloseSql();
            }
        }

        public object ExcuteScarlar(string sp_name, string[] param, object[] values, CommandType commandType)
        {
            object ScalarResult = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                if ((!(param == null)))
                {
                    int len = param.Length - 1;
                    for (int i = 0; i <= len; i++)
                        command.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
                ScalarResult = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                CloseSql();
            }
            return (object)ScalarResult;
        }

        public object ExcuteScarlar(string sp_name, CommandType commandType)
        {
            object ScalarResult = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                ScalarResult = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                CloseSql();
            }
            return (object)ScalarResult;
        }

        public SqlDataReader ExeSP_DataReader(string sp_name, CommandType commandType)
        {
            SqlDataReader dr = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                //CloseSql();
            }
            return dr;
        }

        public SqlDataReader ExeSP_DataReader(string sp_name, string[] param, object[] values, CommandType commandType)
        {
            SqlDataReader dr = null;
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sp_name, OpenSqL());
                command.CommandType = commandType;
                command.CommandTimeout = 0;
                if ((!(param == null)))
                {
                    int len = param.Length - 1;
                    for (int i = 0; i <= len; i++)
                        command.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                //            CloseSql();
            }
            return dr;
        }
        #endregion
    }
}
